DAX Power Pivot Time Intelligence - 10 Easy Steps: Standard & 4-4-5 Custom Calendars by Gilly Dow

Author:Gilly Dow
Language: eng
Format: azw3, epub
Published: 2017-07-04T07:00:00+00:00

Now we know that – let’s crack on with some examples

No of Customers Earning Over £50000 with FILTER

Create the following measure in the Customers table, formatted as a number, 0 decimal places and with a 1000 separator

Wrapping the FILTER() function with a COUNTROWS() function takes care of the “virtual” table created from the FILTER() function i.e. COUNTROWS() counts the rows in the “virtual” table and a single value is returned – YAY!

You have seen it’s possible to create 2 different measures to return the same results using CALCULATE() or FILTER():

=CALCULATE([No of Customers], Customers[YearlyIncome] > 50000)

=COUNTROWS(FILTER(Customers, Customers[YearlyIncome] > 50000))

This is due to the fact there is a column name on the left and a value on the right in the simple filter argument of the CALCULATE() function.

So why bother I hear you ask? Well, as mentioned earlier, the CALCULATE() function is optimised to perform these simple filter functions at lightning speed and you really should use CALCULATE() wherever and whenever possible.

FILTER() comes at a cost in terms of speed and processing. Therefore, if your data model is large, FILTER() in place of CALCULATE() can be “expensive”.

There is another reason. We all love CALCULATE() right? HOWEVER, with its simple filter limitation, what do you do when you need something a little more complicated… I’m thinking custom Time Intelligence here and that’s where we’re heading. But, before we get there, let’s do some more with FILTER() and get really comfortable with it.

CALCULATE() and FILTER() Together! – Say What?!?

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.